{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Challenge\n",
    "\n",
    "Define a a sales history table partitioned by month of sale:\n",
    "\n",
    "- Months are numbers from 1 to 12\n",
    "- Attributes should include:\n",
    "    - product ID\n",
    "    - product name\n",
    "    - product type\n",
    "    - total units sold\n",
    "    - month of sale\n",
    "- Create a primary key using month of sale and product ID"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Solution"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Create table with partitioned by range**\n",
    "\n",
    "`CREATE TABLE sales_history(\n",
    "    product_id INT NOT NULL,\n",
    "\tproduct_name CHARACTER VARYING(50) NOT NULL,\n",
    "\tproduct_type CHARACTER VARYING(50) NOT NULL,\n",
    "\ttotal_units_sold INT NOT NULL,\n",
    "\tmonth_of_sale INT NOT NULL,\n",
    "\tPRIMARY KEY(month_of_sale, product_id)\n",
    ") PARTITION BY RANGE(month_of_sale);`\n",
    "\n",
    "\n",
    "**create partition nodes tables**\n",
    "\n",
    "`CREATE TABLE sales_history_month_1 PARTITION OF sales_history\n",
    "FOR VALUES FROM (1) TO (2);`\n",
    "\n",
    "`CREATE TABLE sales_history_month_2 PARTITION OF sales_history\n",
    "FOR VALUES FROM (2) TO (3);`\n",
    "\n",
    "`CREATE TABLE sales_history_month_3 PARTITION OF sales_history\n",
    "FOR VALUES FROM (3) TO (4);`\n",
    "\n",
    "`CREATE TABLE sales_history_month_4 PARTITION OF sales_history\n",
    "FOR VALUES FROM (4) TO (5);`\n",
    "\n",
    "`CREATE TABLE sales_history_month_5 PARTITION OF sales_history\n",
    "FOR VALUES FROM (5) TO (6);`\n",
    "\n",
    "`CREATE TABLE sales_history_month_6 PARTITION OF sales_history\n",
    "FOR VALUES FROM (6) TO (7);`\n",
    "\n",
    "`CREATE TABLE sales_history_month_7 PARTITION OF sales_history\n",
    "FOR VALUES FROM (7) TO (8);`\n",
    "\n",
    "`CREATE TABLE sales_history_month_8 PARTITION OF sales_history\n",
    "FOR VALUES FROM (8) TO (9);`\n",
    "\n",
    "`CREATE TABLE sales_history_month_9 PARTITION OF sales_history\n",
    "FOR VALUES FROM (9) TO (10);`\n",
    "\n",
    "`CREATE TABLE sales_history_month_10 PARTITION OF sales_history\n",
    "FOR VALUES FROM (10) TO (11);`\n",
    "\n",
    "`CREATE TABLE sales_history_month_11 PARTITION OF sales_history\n",
    "FOR VALUES FROM (11) TO (12);`\n",
    "\n",
    "`CREATE TABLE sales_history_month_12 PARTITION OF sales_history\n",
    "FOR VALUES FROM (12) TO (13);`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
